﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Zgq.CommonTools
{
    public partial class C5DataBaseHelper
    {
        /// <summary>
        /// 获取对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="childModelList"></param>
        /// <returns></returns>
        public List<T> SelectList<T>(string whereStr = null, string orderByStr = null, List<object> childModelList = null)
        {
            var list = GetModelList<T>(SqlDBObject.SelectList<T>(sysDatabaseName, whereStr, orderByStr));
            return list;
        }

        /// <summary>
        /// 获取分页对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public List<T> SelectListPaging<T>(int pageIndex, int pageSize, string whereStr = null, string orderByStr = null)
        {
            return GetModelList<T>(SqlDBObject.SelectListPaging<T>(pageIndex, pageSize, sysDatabaseName, whereStr, orderByStr));
        }

        /// <summary>
        /// 获取分页对象集合 只查询部分列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public List<T> SelectListPagingColumn<T>(int pageIndex, int pageSize, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            return GetModelList<T>(SqlDBObject.SelectListPagingColumn<T>(pageIndex, pageSize, sysDatabaseName, whereStr, orderByStr));
        }

        /// <summary>
        /// 获取对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public T SelectModel<T>(string whereStr = null, string orderByStr = null)
        {
            return GetModel<T>(SqlDBObject.SelectModel<T>(sysDatabaseName, whereStr, orderByStr));
        }

        /// <summary>
        /// 查询总量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public int SelectModelCount<T>(string whereStr = null)
        {
            return ExecuteScalar(SqlDBObject.SelectModelCount<T>(sysDatabaseName, whereStr)).ToInt();
        }

        /// <summary>
        /// 查询DataTable数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public DataTable SelectDatatable(string tableName, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            return GetDataTable(SqlDBObject.SelectDatatable(tableName, sysDatabaseName, whereStr, orderByStr, colums));
        }

        /// <summary>
        /// 查询DataTable数据带分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="tableName"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        public DataTable SelectDatatablePaging(string tableName, int pageIndex, int pageSize, string whereStr = null, string orderByStr = null, params string[] colums)
        {
            return GetDataTable(SqlDBObject.SelectDatatablePaging(tableName, pageIndex, pageSize, sysDatabaseName, whereStr, orderByStr, colums));
        }

        /// <summary>
        /// 查询DataTable数据量
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public int SelectDatatableCount(string tableName, string whereStr = null)
        {
            return ExecuteScalar(SqlDBObject.SelectDatatableCount(tableName, sysDatabaseName, whereStr)).ToInt();
        }

        /// <summary>
        /// 获取视图对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public List<T> SelectViewModelList<T>(string whereStr = null, string orderByStr = null)
        {
            //查询主表数据
            var masterList = GetModelList<T>(SqlDBObject.GetViewListSql<T>(whereStr, orderByStr));
            if (masterList == null || masterList.Count == 0) return masterList;

            //查询子表数据
            GetChildList(masterList);

            return masterList;
        }

        /// <summary>
        /// 获取视图分页对象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderByStr"></param>
        /// <returns></returns>
        public List<T> SelectViewModelListPaging<T>(int pageIndex, int pageSize, string whereStr = null, string orderByStr = null)
        {
            //查询主表数据
            var masterList = GetModelList<T>(SqlDBObject.GetViewListPagingSql<T>(pageIndex, pageSize, whereStr, orderByStr));
            if (masterList == null || masterList.Count == 0) return masterList;

            //查询子表数据
            GetChildList(masterList);

            return masterList;
        }

        #region 查询子表数据
        /// <summary>
        /// 查询子表数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="masterList"></param>
        public void GetChildList<T>(List<T> masterList)
        {
            //主表属性
            List<PropertyInfo> propertyInfoList = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public)
                                                  .Where(o => o.GetCustomAttributes(typeof(C5ViewChildListAttribute), false).Length > 0).ToList();

            if (propertyInfoList == null || propertyInfoList.Count == 0) return;

            foreach (var pro in propertyInfoList)
            {
                var attr = pro.GetCustomAttributes(typeof(C5ViewChildListAttribute), false);

                var c5ViewChildModel = attr[0] as C5ViewChildListAttribute;

                DataTable dt = null;

                List<string> sqlStrList = new List<string>();

                StringBuilder sqlBuilder = new StringBuilder();

                //组合SQL条件
                for (int i = 0; i < masterList.Count; i++)
                {
                    if (i > 0) sqlBuilder.Append(" OR ");

                    sqlBuilder.Append(" ( ");

                    //获取子明细条件字符串
                    sqlBuilder.Append(GetChildWhereInfo(pro, c5ViewChildModel.WhereInfo, masterList[i]));

                    sqlBuilder.Append(" ) ");

                    if (sqlBuilder.Length > 5000 || (i + 1) == masterList.Count)
                    {
                        sqlStrList.Add(sqlBuilder.ToString());
                        sqlBuilder.Clear();
                    }
                }

                //查询数据
                foreach (var item in sqlStrList)
                {
                    DataTable tempTable = GetDataTable(string.Format(" SELECT * FROM {0} WHERE 1 = 1 AND ({1}) ", !string.IsNullOrEmpty(c5ViewChildModel.SourceName) ? c5ViewChildModel.SourceName : c5ViewChildModel.EntityType.Name, item));
                    if (tempTable == null) continue;

                    if (dt != null)
                        foreach (DataRow row in tempTable.Rows) dt.ImportRow(row);
                    else
                        dt = tempTable.Copy();
                }

                foreach (var master in masterList)
                {
                    //获取子明细条件字符串
                    string whereInfoStr = GetChildWhereInfo(pro, c5ViewChildModel.WhereInfo, master);

                    //查询当前主表下挂的明细信息
                    var rows = dt.Select(whereInfoStr).ToList();
                    if (rows == null || rows.Count == 0) continue;

                    //将DataRow转换成对象
                    var listToTableMethod = typeof(PublicHelper).GetMethod("DataRowsToList");
                    MethodInfo convertMethod = listToTableMethod.MakeGenericMethod(new Type[] { c5ViewChildModel.EntityType });
                    var tempList = convertMethod.Invoke(this, new object[] { rows });

                    pro.SetValue(master, tempList, null);
                }
            }
        }
        #endregion

        #region 获取子明细条件字符串
        /// <summary>
        /// 获取子明细条件字符串
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pro"></param>
        /// <param name="whereInfo"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        private string GetChildWhereInfo<T>(PropertyInfo pro, string whereInfo, T item)
        {
            while (true)
            {
                if (!whereInfo.Contains("<") || !whereInfo.Contains(">")) break;

                int startIndex = whereInfo.IndexOf("<");

                //取出字段名
                string tempStr = whereInfo.Substring(startIndex + 1);

                int endIndex = tempStr.IndexOf(">");

                string fieldStr = tempStr.Substring(0, endIndex);

                Type viewType = item.GetType();

                var fieldPro = viewType.GetProperty(fieldStr);
                if (fieldPro == null) throw new Exception(string.Format("实体【{0}】无【{1}】字段，请检查！", viewType.Name, fieldStr));

                //反射取值
                string valueStr = string.Format(" '{0}' ", fieldPro.GetValue(item, null).ToString());

                whereInfo = whereInfo.Replace(string.Format("<{0}>", fieldStr), valueStr);
            }

            return whereInfo;
        }
        #endregion

        public int Insert(object model)
        {
            List<object> sqlParameters = new List<object>();
            return ExecteNonQuery(SqlDBObject.GetInsertSql(model, sysDatabaseName, sqlParameters), CommandType.Text, sqlParameters.Cast<SqlParameter>().ToArray());
        }

        public int Update(object model)
        {
            List<object> sqlParameters = new List<object>();
            string strSql = SqlDBObject.GetUpdateSql(model, sysDatabaseName, sqlParameters);
            int i = ExecteNonQuery(strSql, CommandType.Text, sqlParameters.Cast<SqlParameter>().ToArray());
            if (i > 1)
            {
                LogHelper.Error(string.Format("更新时匹配到{0}条数据,不能确定数据唯一性,\r\n语句：{1}", i, strSql));
                throw new Exception(string.Format("更新时匹配到{0}条数据,不能确定数据唯一性", i));
            }
            return i;
        }

        public int DeleteList(params object[] lists)
        {
            List<object> sqlParameters = new List<object>();
            return ExecteNonQuery(SqlDBObject.GetDeleteListSql(lists, sysDatabaseName, sqlParameters), CommandType.Text, sqlParameters.Cast<SqlParameter>().ToArray());
        }

        public int Delete(object model)
        {
            List<object> sqlParameters = new List<object>();
            string strSql = SqlDBObject.GetDeleteSql(model, sysDatabaseName, sqlParameters);
            int i = ExecteNonQuery(strSql, CommandType.Text, sqlParameters.Cast<SqlParameter>().ToArray());
            if (i > 1)
            {
                LogHelper.Error(string.Format("删除时匹配到{0}条数据,不能确定数据唯一性,\r\n语句：{1}", i, strSql));
                throw new Exception(string.Format("删除时匹配到{0}条数据,不能确定数据唯一性", i));
            }

            return i;
        }

        public int Delete(string sqlStr)
        {
            return ExecteNonQuery(sqlStr);
        }

        public bool IsExists(object model, string whereStr = null)
        {
            return Exists(SqlDBObject.GetExistsSql(model, sysDatabaseName, whereStr));
        }

        public long GetNumber(string entityName)
        {
            return GetNumberStatic(this, entityName);
        }

        public void SetNumber(object obj, string PropertyInfo)
        {
            SetNumberStatic(this, obj, PropertyInfo);
        }

        public List<T> GetPageListT<T>(string sqlStr, string orderByStr, int pageSize, int pageIndex, out int iCount)
        {
            if (string.IsNullOrEmpty(orderByStr))
                throw new Exception("排序条件不能为空！");

            string countSqlStr = string.Format("select count(1) numCount from ({0}) couttb", sqlStr);

            string sqlListStr = null;
            if (pageIndex > 0)
            {
                StringBuilder pageSqlStr = new StringBuilder();
                pageSqlStr.Append("with Table_CTE as ");

                pageSqlStr.AppendFormat("(select ROW_NUMBER() OVER(ORDER BY {0}) page_num,* from ({1}) as tempaddRowNumberDt)", orderByStr, sqlStr);
                pageSqlStr.AppendFormat(" select top {0} * From Table_CTE where page_num > {1}", pageSize, (pageIndex - 1) * pageSize);
                sqlListStr = pageSqlStr.ToString();
            }
            else
                sqlListStr = sqlStr;

            iCount = (int)this.ExecuteScalar(countSqlStr);
            var list = this.GetModelList<T>(sqlListStr);

            return list;
        }
    }
}
